/**
 * Class:   AdviserManager
 * 
 * Methods:
 * 
 *      boolean insertAdviser( String adviser );
 *      
 *      ArrayList<String> searchAdviser( int activityID );
 *      
 *      public ArrayList<String> viewAdviser();
 *      
 *      void deleteAdviserWithNoActivity();
 *      
 *      int getAdviserID( String adviser );
 * 
 * Remarks:
 * 
 * 		This class should not be touched by the controllers.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class AdviserManager extends DatabaseManager {
    
    /**
     * Constructors
     */
    
    public AdviserManager() {}
    
    
    
    /**
     *  Insert Adviser
     *  	- inserts an adviser into the adviser database
     *  
     *  Parameters
     *		- String adviser: the adviser to be added
     *	
     *	Returns
     *		- boolean true: adviser is successfully inserted
     * 		- boolean false: insertion failed
     */
    boolean insertAdviser( String adviser ) {
    	try {
    		/* Declaration of Block Variables */
        	PreparedStatement preparedStatement;
    		
        	connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "INSERT INTO `adviser`" +
    											   " (`adviserName`)" +
    											   " VALUES (?)" );
    		
    		preparedStatement.setString( 1, adviser );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getAdviserID( adviser ) != 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
	
	
	
	/**
	 *  Search Adviser
	 *  	- gets all advisers in a certain activity
	 *  	  from the adviser database
	 *  
	 *  Parameters
	 *		- int activityID: the ID of the activity the advisers
	 *						  are from 
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no advisers found
	 * 		- ArrayList<String>.size() > 0: advisers found
	 */
	ArrayList<String> searchAdviser( int activityID ) {
		
		/* Declaration of Local Variables */
		ArrayList<String> adviserList = new ArrayList<String>();
		
		try {
    		/* Declaration of Block Variables */
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			resultSet = statement
						.executeQuery( "SELECT * FROM `adviser` WHERE" +
									   " `adviserID` IN (SELECT `adviserID`" +
									   " FROM `v_activityAdviser` WHERE" +
									   " `activityID` = " + activityID + ")" );
			
			while ( resultSet.next() ) {
				adviserList.add( resultSet.getString( "adviserName" ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return adviserList;
	}
	
	
	
	/**
	 *  View Adviser
	 *  	- gets all advisers from the adviser database
	 *  
	 *  Parameters
	 *		- n/a
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no advisers found
	 * 		- ArrayList<String>.size() > 0: advisers found
	 */
	public ArrayList<String> viewAdviser() {
		
		/* Declaration of Local Variables */
		ArrayList<String> adviserList = new ArrayList<String>();
		
		try {
    		/* Declaration of Block Variables */
			PreparedStatement preparedStatement;
			ResultSet resultSet;
			
			connect();
			
			preparedStatement = getConnection().prepareStatement( "SELECT * FROM `adviser`" );
			
			resultSet = preparedStatement.executeQuery();
			
			while ( resultSet.next() ) {
				adviserList.add( resultSet.getString( "adviserName" ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return adviserList;
	}
    
    
    
    /**
     *  Delete Adviser with No Activity
     *  	- deletes an adviser with no corresponding 
     *  	  activity in the v_activityAdviser database
     *  
     *  Parameters
     *		- n/a
     *	
     *	Returns
     *		- n/a
     */
    void deleteAdviserWithNoActivity() {
    	try {
    		/* Declaration of Block Variables */
    		Statement statement;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		statement.executeUpdate( "DELETE FROM `adviser` WHERE `adviserID`" +
    								 " NOT IN (SELECT DISTINCT `adviserID`" +
    								 " FROM `v_activityAdviser`)" );
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    }
    
    
    
    /**
     *  Get Adviser ID
     *  	- finds the ID of the adviser in the adviser database
     *  
     *  Parameters
     *		- String adviser: the adviser to be found
     *	
     *	Returns
     *		- non-zero int: adviser ID found
     * 		- int 0: adviser not found
     */
    int getAdviserID( String adviser ) {
    	try {
    		/* Declaration of Block Variables */
    		int adviserID = 0;
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		resultSet = statement
    					.executeQuery( "SELECT `adviserID` FROM `adviser` " +
    							   	   "WHERE `adviserName` = \"" +
    							   	   adviser + "\"" );
    		
    		while ( resultSet.next() ) {
    			adviserID = resultSet.getInt( "adviserID" );
    		}
    		
    		resultSet.close();
    		disconnect();
    		return adviserID;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return 0;
    }
}
